/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package persistence;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.*;

/**
 *
 * @author isen
 */
public class PersistenceImplementation implements PersistenceInterface {

    private static final PersistenceImplementation persistenceManager = new PersistenceImplementation();
    private final Object lockOfTheConexion = new Object();
    private Connection conexion = null;
    private static final Logger logger = Logger.getLogger(PersistenceImplementation.class.getName());

    private PersistenceImplementation() {
    }

    public static PersistenceImplementation getJDBCPersistenceManager() {
        return persistenceManager;
    }

    @Override
    public int getCurrentCartId(String clientname) {
        int currentid;
        int id = hasAShoppingCartUnpaid(clientname);
        if (id != -1) {
            currentid = id;
            return currentid;
        } else {
            currentid = nextCartId();
            Cart cart = new Cart(currentid, clientname, 0);
            createACart(cart);
            return currentid;
        }
    }

    @Override
    public boolean checkCombination(String username, String password) {
        String query = "select * from \"USERS\" where USERNAME = ? AND PASSWORD = ?";
        PreparedStatement statement;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, username);
            statement.setString(2, password);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al authentificacion del usuario", ex);
            return false;
        }
    }

    @Override
    public boolean isClient(String username) {
        String query = "select * from \"USERS\" where USERNAME = ? AND STATUS = ?";
        PreparedStatement statement;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, username);
            statement.setString(2, "c");
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al authentificacion del usuario", ex);
            return false;
        }
    }

    @Override
    //TODO: changer le type de Format de Pago, mettre > 20 
    public boolean createUser(User user) {
        String query = "insert into\"USERS\" values(?,?,?,?,?,?)";
        PreparedStatement statement;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, user.getName());
            statement.setString(2, user.getAddress());
            statement.setString(3, user.getFormatDePago());
            //statement.setObject(4, user.getStatus(), java.sql.Types.CHAR);
            statement.setString(4, user.getStatus());
            statement.setString(5, user.getPassword());
            statement.setString(6, user.getUsername());
            statement.execute();
            return true;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al crear un usuario", ex);
            return false;
        }
    }

    @Override
    public boolean deleteUser(String name) {
        String query = "delete from \"USERS\" where NOMBRE = ?";
        PreparedStatement statement;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, name);
            statement.execute();
            return true;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al borrar un usuario", ex);
            return false;
        }
    }

    @Override
    public boolean updateUser(String name, User user) {
        String query = "update \"USERS\" SET NOMBRE =?,DIRECCION = ?,FORMATDEPAGO = ?,STATUS= ?,"
                + " PASSWORD = ?, USERNAME = ? WHERE NOMBRE = ? ";
        PreparedStatement statement;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, user.getName());
            statement.setString(2, user.getAddress());
            statement.setString(3, user.getFormatDePago());
            //statement.setObject(4, user.getStatus(), java.sql.Types.CHAR);
            statement.setString(4, user.getStatus());
            statement.setString(5, user.getPassword());
            statement.setString(6, user.getUsername());
            statement.setString(7, name);
            statement.execute();
            return true;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al actualizar un amigo", ex);
            return false;
        }
    }

    @Override
    public User readUser(String name) {
        String query = "select * from \"USERS\" where NOMBRE =?";
        PreparedStatement statement;
        ResultSet resultSet;
        User user = null;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, name);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                user = new User();
                user.setAddress(resultSet.getString("DIRECCION"));
                user.setUsername(resultSet.getString("USERNAME"));
                user.setFormatDePago(resultSet.getString("FORMATDEPAGO"));
                user.setName(resultSet.getString("NOMBRE"));
                user.setPassword(resultSet.getString("PASSWORD"));
                user.setStatus(resultSet.getString("STATUS"));
                //user.setStatus(resultSet.getString("STATUS").charAt(0));
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al recuperar un usuario", ex);
            user = null;
        }
        return user;
    }

    @Override
    public User getUser(String username) {
        String query = "select * from \"USERS\" where NOMBRE =?";
        PreparedStatement statement;
        ResultSet rs;
        User user;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, username);
            rs = statement.executeQuery();
            while (rs.next()) {
                user = new User(rs.getString("NOMBRE"), rs.getString("DIRECCION"), username, rs.getString("FORMATDEPAGO"), rs.getString("PASSWORD"), rs.getString("STATUS"));
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Ha ocurrido un error obteniendo el usuario de la BD", ex);
        }
        return null;
    }

    @Override
    public boolean createProduct(Product product) {
        String query = "insert into\"PRODUCT\" values(?,?,?,?,?)";
        PreparedStatement statement;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, product.getNombre());
            statement.setInt(2, product.getStock());
            statement.setString(3, product.getDescripcionCorta());
            statement.setString(4, product.getDescripcionLarga());
            statement.setDouble(5, product.getPrecio());
            statement.execute();
            return true;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al crear un producto", ex);
            return false;
        }
    }

    @Override
    public boolean productExists(String name) {
        String query = "select * from \"PRODUCT\" where NAME = ?";
        PreparedStatement statement;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, name);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                System.out.println("oui le produit existe deja");
                return true;
            } else {
                System.out.println("ce produit n'existe pas encore");
                return false;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al verificar la existencia del producto", ex);
            return false;
        }
    }

    @Override
    public boolean deleteProduct(String name) {
        String query = "delete from \"PRODUCT\" where NAME = ?";
        PreparedStatement statement;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, name);
            statement.execute();
            return true;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al borrar un producto", ex);
            return false;
        }
    }

    @Override
    public HashMap<String, Product> getProducts() {
        String query = "select * from \"PRODUCT\" ";

        PreparedStatement statement;
        ResultSet resultSet;
        HashMap<String, Product> productos = new HashMap<String, Product>();

        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Product prod = new Product(resultSet.getString("NAME"), resultSet.getDouble("PRICE"), resultSet.getInt("STOCK"), resultSet.getString("SHORTDESCRIPTION"));
                productos.put(prod.getNombre(), prod);
            }
            return productos;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al recuperar los productos", ex);
            return null;
        }

    }

    @Override
    public Product readProduct(String name) {
        String query = "select * from \"PRODUCT\" where NAME =?";
        PreparedStatement statement;
        ResultSet resultSet;
        Product product = null;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, name);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                product = new Product();
                product.setNombre(resultSet.getString("NAME"));
                product.setStock(resultSet.getInt("STOCK"));
                product.setDescripcionCorta(resultSet.getString("SHORTDESCRIPTION"));
                product.setPrecio(resultSet.getDouble("PRICE"));
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al leer un producto", ex);
            product = null;
        } 
        return product;
    }

    @Override
    public Product readDetailsProduct(String name) {
        String query = "select * from \"PRODUCT\" where NAME =?";
        PreparedStatement statement;
        ResultSet resultSet = null;
        Product product = null;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, name);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                product = new Product();
                product.setNombre(resultSet.getString("NAME"));
                product.setStock(resultSet.getInt("STOCK"));
                product.setDescripcionCorta(resultSet.getString("SHORTDESCRIPTION"));
                product.setDescripcionLarga(resultSet.getString("LONGDESCRIPCION"));
                product.setPrecio(resultSet.getDouble("PRICE"));
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al recuperar un producto", ex);
            product = null;

        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException ex) {
                    logger.log(Level.SEVERE, "Error al cerrar la conexon a la base de datos", ex);
                }
            }
        }
        return product;
    }

    @Override
    public boolean updateProduct(String name, Product product) {
        String query = "update \"PRODUCT\" SET NAME =?,STOCK = ?,SHORTDESCRIPTION = ?,LONGDESCRIPCION= ?,"
                + " PRICE = ? WHERE NAME = ?";
        PreparedStatement statement;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, product.getNombre());
            statement.setInt(2, product.getStock());
            statement.setString(3, product.getDescripcionCorta());
            statement.setString(4, product.getDescripcionLarga());
            statement.setDouble(5, product.getPrecio());
            statement.setString(6, name);
            statement.execute();
            return true;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al actualizar un producto", ex);
            return false;
        }
    }

    @Override
    public boolean addProductToShoppingCart(String productname, int quantity, int cartid) {
        String query = "insert into\"CARTCONTENTS\" values(?,?,?)";
        PreparedStatement statement;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, productname);
            statement.setInt(2, quantity);
            statement.setInt(3, cartid);
            statement.execute();
            return true;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error to add a shoppingcart", ex);
            return false;
        }
    }

    @Override
    public int hasAShoppingCartUnpaid(String username) {
        String query = "select * from \"CART\" where USERNAME =? AND PAID = 0";
        PreparedStatement statement;
        ResultSet rs;
        int id = -1;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, username);
            rs = statement.executeQuery();
            if (rs.next()) {
                id = rs.getInt("CARTID");
            }
            return id;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error to check if the cart is paid", ex);
            return id;
        }
    }

    @Override
    public int nextCartId() {
        String query = "select CARTID from \"CART\" order by CARTID desc";
        PreparedStatement statement;
        ResultSet rs;
        int id = 1;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            rs = statement.executeQuery();
            if (rs.next()) {
                id = rs.getInt("CARTID") + 1;
            }
            return id;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al recupera el nextCartId", ex);
            return id;
        }
    }

    @Override
    public boolean isAlreadyAdded(int cartid, String productname) {
        String query = "select * from \"CARTCONTENTS\" where PRODUCTNAME =? AND CARTID = ?";
        PreparedStatement statement;
        ResultSet rs;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, productname);
            statement.setInt(2, cartid);
            rs = statement.executeQuery();
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error to isAlreadyAdded", ex);
            return false;
        }
    }

    @Override
    public boolean updateQuantityOfACart(int cartid, String productname, int newamount) {
        String query = "update \"CARTCONTENTS\" SET QUANTITY =? where PRODUCTNAME = ? AND CARTID = ?";
        PreparedStatement statement;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setInt(1, newamount);
            statement.setString(2, productname);
            statement.setInt(3, cartid);
            statement.execute();
            return true;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error to update the quantity", ex);
            return false;
        }
    }

    @Override
    public int getAmountFromACart(int cartid, String productname) {
        String query = "select * from \"CARTCONTENTS\" where PRODUCTNAME = ? AND  CARTID= ?";
        PreparedStatement statement;
        ResultSet resultSet;
        int amount = 0;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, productname);
            statement.setInt(2, cartid);
            resultSet = statement.executeQuery();
            resultSet.next();
            amount = resultSet.getInt("QUANTITY");
            return amount;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error to get the amount from a cart", ex);
            return amount;
        }
    }

    @Override
    public int getStock(String productname) {
        String query = "select * from \"PRODUCT\" where NAME = ? ";
        PreparedStatement statement;
        ResultSet resultSet;
        int stock = 0;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, productname);
            resultSet = statement.executeQuery();
            resultSet.next();
            stock = resultSet.getInt("STOCK");
            return stock;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al recuperar el stock", ex);
            return stock;
        }
    }

    @Override
    public double getPriceOfAProdcut(String productname) {
        String query = "select * from \"PRODUCT\" where NAME = ? ";
        PreparedStatement statement;
        ResultSet resultSet;
        double price = 0;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, productname);
            resultSet = statement.executeQuery();
            resultSet.next();
            price = resultSet.getDouble("PRICE");
            return price;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al recuperar un precio de un producto", ex);
            return price;
        }
    }

    @Override
    public boolean createACart(Cart cart) {
        String query = "insert into\"CART\" values(?,?,?)";
        PreparedStatement statement;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setInt(1, cart.getCartid());
            statement.setString(2, cart.getUsername());
            statement.setInt(3, cart.getPaid());
            statement.execute();
            return true;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al crear un ShoppingCART", ex);
            return false;
        }
    }

    @Override
    public double getFinalPrice(int cartid) {
        String query = "Select sum(cc.QUANTITY*p.PRICE) as finalprice from CARTCONTENTS as cc, PRODUCT as p where cc.cartid = ? and p.NAME = cc.PRODUCTNAME group by cc.CARTID";
        PreparedStatement statement;
        ResultSet resultSet;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setInt(1, cartid);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getDouble("finalprice");
            } else {
                return 0;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al recuperar el precio total", ex);
            return 0;
        }
    }

    @Override
    public ArrayList<CartContent> getCartContents(int cartid) {
        String query = "select * from \"CARTCONTENTS\" where CARTID = ?";
        PreparedStatement statement;
        ResultSet resultSet;
        ArrayList<CartContent> cartcontents = new ArrayList<CartContent>();
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setInt(1, cartid);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                CartContent cc = new CartContent(resultSet.getString("PRODUCTNAME"), resultSet.getInt("QUANTITY"));
                cartcontents.add(cc);
            }
            return cartcontents;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error to get the content of the cart", ex);
            return null;
        }
    }

    @Override
    public Transactions getTransaction(int transactionid) {

        String query = "select * from \"TRANSACTIONS\" where ID =?";
        PreparedStatement statement;
        ResultSet resultSet;
        Transactions transaction = null;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setInt(1, transactionid);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                transaction = new Transactions();
                transaction.setId(transactionid);
                transaction.setDate(resultSet.getString("DATE"));
                transaction.setCartid(resultSet.getInt("CARTID"));
                transaction.setClientName(resultSet.getString("CLIENTNAME"));
                transaction.setFinalPrice(resultSet.getInt("FINALPRICE"));
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error to get a transation", ex);
            transaction = null;
        }
        return transaction;
    }
    
    @Override
    public ArrayList<Integer> getTransactionIds() {
        String query = "select ID from \"TRANSACTIONS\" order by DATE desc";
        PreparedStatement statement;
        ResultSet resultSet;
        ArrayList<Integer> listIds = new ArrayList<Integer>();
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                listIds.add(resultSet.getInt("ID"));
            }
            return listIds;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al recuperar la lista de transactionIds", ex);
            return null;
        }

    }

    @Override
    public String getAdress(String clientname) {
        String query = "select * from \"USERS\" where NOMBRE = ? ";
        PreparedStatement statement;
        ResultSet resultSet;
        String address;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, clientname);
            resultSet = statement.executeQuery();
            resultSet.next();
            address = resultSet.getString("DIRECCION");
            return address;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al recuperar un direccion", ex);
            return null;
        }
    }

    @Override
    public String getPaymentform(String clientname) {
        String query = "select * from \"USERS\" where NOMBRE = ? ";
        PreparedStatement statement;
        ResultSet resultSet;
        String paymentform;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setString(1, clientname);
            resultSet = statement.executeQuery();
            resultSet.next();
            paymentform = resultSet.getString("FORMATDEPAGO");
            return paymentform;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al recuperar el format de pago", ex);
            return null;
        }
    }

    @Override
    public int getTransactionID(int cartid) {
        String query = "select * from \"TRANSACTIONS\" where CARTID = ? ";
        PreparedStatement statement;
        ResultSet resultSet;
        int transactionID;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setInt(1, cartid);
            resultSet = statement.executeQuery();
            resultSet.next();
            transactionID = resultSet.getInt("ID");
            return transactionID;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al recuperar un transationid", ex);
            return -1;
        }

    }

    @Override
    public boolean cartIsEmpty(int cartid) {
        String query = "select * from CARTCONTENTS where CARTID = ?";
        PreparedStatement statement;
        ResultSet resultSet;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setInt(1, cartid);
            resultSet = statement.executeQuery();
            return !resultSet.next();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error to check if cart is empty", ex);
            return true;
        }
    }

    @Override
    public boolean setPaid(int cartid) {
        String query = "update CART set PAID = 1 where CARTID = ?";
        PreparedStatement statement;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setInt(1, cartid);
            return statement.executeUpdate() == 1;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error to setPaid", ex);
            return false;
        }

    }

    @Override
    public boolean createTransaction(int cartid, String clientname, double finalprice) {
        String query = "insert into\"TRANSACTIONS\" (DATE, CARTID, CLIENTNAME, FINALPRICE) values(current_timestamp,?,?,?)";
        PreparedStatement statement;
        try {
            synchronized (lockOfTheConexion) {
                statement = conexion.prepareStatement(query);
            }
            statement.setInt(1, cartid);
            statement.setString(2, clientname);
            statement.setDouble(3, finalprice);
            statement.execute();
            return true;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error to create a transaction", ex);
            return false;
        }
    }

    @Override
    public boolean setUp(String url, String driver, String user, String password) {
        try {
            Class.forName(driver);
            conexion = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException ex) {
            logger.log(Level.SEVERE, "No se encontro el driver para la base de datos", ex);
            return false;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "No se pudo establecer la conexion con la base de datos", ex);
            return false;
        }
        return true;
    }

    @Override
    public boolean disconnect() {
        try {
            conexion.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Conexion a la base de datos no cerrada", ex);
            return false;
        }
        return true;
    }
}
